import sqlite3,os,cv2
import numpy as np
from support.const.state import *
class SQLiteHelper:
    TABLE_ACTIVITIES = "_activities"
    TABLE_DEBUG_PER = "debug_"
    MODE_NORMAL = 0
    MODE_DEBUG = 1
    def __autoCreateDir(self, *dirs):
        path = ''
        for dir in dirs:
            path = os.path.join(path,dir)
        if(not os.path.exists(path)):
            os.makedirs(path)
        return path
        
    def __init__(self, path, appname):
        dbpath = os.path.join(path, "db")
        self.__autoCreateDir(dbpath)
        self.conn = sqlite3.connect(os.path.join(dbpath, "{}.db".format(appname)))
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {} (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME VARCHAR(100),STATE INT, DEPTH INT);".format(SQLiteHelper.TABLE_ACTIVITIES))
        self.conn.commit()
        cur.close()
        self.mode = SQLiteHelper.MODE_NORMAL
        
    def setMode(self, mode):
        self.mode = mode
        
    def __createActivityTable(self, activity):
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS '{}' (ID INTEGER PRIMARY KEY AUTOINCREMENT,COMP BLOB, JUMPAID INT, depth INT);".format(activity))
        self.conn.commit()
        cur.close()
        
    def close(self):
        self.conn.close()
        
    def updateComponentdepthById(self, activity, cmpid, depth):
        activity = self.__autoInsertDebugPre(activity)
        cur = self.conn.cursor()
        cur.execute("UPDATE '{}' SET depth = {} WHERE ID = {}".format(activity, depth, cmpid))
        _id = cur.lastrowid
        self.conn.commit()
        cur.close()
        
    def __insertNewCompForActivity(self, activity, comp, jumpid, depth):
        _id = 0
        cur = self.conn.cursor()
        comp_blob = np.array(cv2.imencode('.png', comp)[1]).tobytes()
        cur.execute("INSERT INTO '{}' (COMP, JUMPAID, depth) VALUES (?, ?, ?)".format(activity), (comp_blob, jumpid, depth))
        rowid = cur.lastrowid
        self.conn.commit()
        cur.close()
        return rowid
        
    def updateActivityState(self, activity, state, depth):
        activity = self.__autoInsertDebugPre(activity)
        _id = 0
        cur = self.conn.cursor()
        result = cur.execute("SELECT ID, STATE FROM {} WHERE NAME='{}'".format(SQLiteHelper.TABLE_ACTIVITIES, activity))
        values = result.fetchall()
        if(values):
            _id = values[0][0]
            if(values[0][1] != state and state != 0):
                cur.execute("UPDATE {} SET STATE = {} WHERE ID = {}".format(SQLiteHelper.TABLE_ACTIVITIES, state, _id))
        else:
            cur.execute("INSERT INTO {} (NAME, STATE, DEPTH) VALUES ('{}', {}, {})".format(SQLiteHelper.TABLE_ACTIVITIES, activity, state, depth))
            _id = cur.lastrowid
            self.conn.commit()
        cur.close()
        return _id
        
    def autoUpdateActivityInfo(self, from_act, comp, depth, jump_act, from_state, jump_state = ActivityState.NEWONE):
        self.updateActivityState(from_act, from_state, depth)
        jump_id = self.updateActivityState(jump_act, jump_state, depth - 1)
        if(comp is not None):
            from_act = self.__autoInsertDebugPre(from_act)
            self.__insertNewCompForActivity(from_act, comp, jump_id, depth)
            
    def getActivityState(self, activity):
        activity = self.__autoInsertDebugPre(activity)
        cur = self.conn.cursor()
        result = cur.execute("SELECT ID, STATE, DEPTH FROM {} WHERE NAME='{}'".format(SQLiteHelper.TABLE_ACTIVITIES, activity))
        values = result.fetchall()
        cur.close()
        if(values):
            return (values[0][0], values[0][1], values[0][2])
        else:
            return None

    def deleteDebugTable(self):
        cur = self.conn.cursor()
        values = cur.execute("SELECT ID, NAME FROM {} WHERE NAME like '{}%';".format(SQLiteHelper.TABLE_ACTIVITIES, SQLiteHelper.TABLE_DEBUG_PER)).fetchall()
        for value in values:
            cur.execute("DELETE FROM {} WHERE ID = {};".format(SQLiteHelper.TABLE_ACTIVITIES, value[0]))
            cur.execute("DROP TABLE '{}';".format(value[1]))
            # cur.execute("DELETE FROM '{}';".format(value[1]))
            # cur.execute("DELETE FROM sqlite_sequence WHERE name = '{}';".format(value[1]))
        self.conn.commit()
        cur.close()

    def getAllCompsFrom(self, activity):
        activity = self.__autoInsertDebugPre(activity)
        self.__createActivityTable(activity)
        cur = self.conn.cursor()
        result = cur.execute("SELECT '{}'.ID, COMP, '{}'.depth, {}.state FROM '{}' INNER JOIN {} WHERE '{}'.JUMPAID = {}.ID;".format(activity, activity, SQLiteHelper.TABLE_ACTIVITIES, activity, SQLiteHelper.TABLE_ACTIVITIES, activity, SQLiteHelper.TABLE_ACTIVITIES))
        values = result.fetchall()
        if(values):
            return iter(ImageDB(values))
        else:
            return None
            
    def __autoInsertDebugPre(self, activity):
        if(activity is not None and self.mode == SQLiteHelper.MODE_DEBUG):
            return "{}{}".format(SQLiteHelper.TABLE_DEBUG_PER, activity)
        return activity
        
    def __getActivityInfo(self, activity):
        cur = self.conn.cursor()
        if(activity):
            result = cur.execute("SELECT NAME FROM {} WHERE NAME='{}'".format(SQLiteHelper.TABLE_ACTIVITIES, activity))
        else:
            result = cur.execute("SELECT NAME FROM {}".format(SQLiteHelper.TABLE_ACTIVITIES))
        values = result.fetchall()
        cur.close()
        return values
        
    def getAllBlobFromActivity(self, activity, func):
        activity = self.__autoInsertDebugPre(activity)
        if(activity is None):
            activies = self.__getActivityInfo(activity)
        else:
            activies = [[activity]]
        cur = self.conn.cursor()
        for act in activies:
            if(act[0] == '<jump_out>'):
                continue
            values = cur.execute("SELECT ID, COMP FROM '{}'".format(act[0])).fetchall()
            for blob in values:
                func('{} : {}'.format(act[0], blob[0]), ImageDB.getImg(blob[1]), timeout = Global.BLOB_SHOW_TIMEOUT)
        cur.close()
class ImageDB:
    def __init__(self, values):
        self.values = values;
    def __iter__(self):
        self.valiter = iter(self.values)
        return self
    def __next__(self):
        value = next(self.valiter)
        return (value[0], ImageDB.getImg(value[1]), value[2], value[3])
    def remove(self, idx):
        for value in self.values[::-1]:
            if(value[0] in idx):
                self.values.remove(value)
        self.valiter = iter(self.values)
    def __len__(self):
        return len(self.values)
    @staticmethod
    def getImg(value):
        return cv2.imdecode(np.asarray(bytearray(value), dtype="uint8"), cv2.IMREAD_COLOR)
